# 修改元数据表结构
METADATA_TABLE = """
CREATE TABLE IF NOT EXISTS sync_metadata (
    table_name VARCHAR(255) PRIMARY KEY,
    last_sync_id INT DEFAULT 0,
    last_sync_time DATETIME,
    sync_strategy ENUM('id', 'timestamp', 'full') NOT NULL,
    initial_sync BOOLEAN DEFAULT FALSE
)
"""

def get_table_structure(cursor, table_name):
    """获取表结构信息"""
    cursor.execute(f"""
        SELECT COLUMN_NAME, DATA_TYPE, COLUMN_KEY 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = %s
    """, (table_name,))
    return cursor.fetchall()

def determine_sync_strategy(cursor, table_name):
    """确定同步策略"""
    columns = get_table_structure(cursor, table_name)
    
    has_auto_increment = any(
        col.COLUMN_KEY == 'PRI' and 'auto_increment' in col.EXTRA 
        for col in columns
    )
    
    has_update_time = any(
        col.COLUMN_NAME.lower() == 'update_time' 
        and col.DATA_TYPE in ('datetime', 'timestamp')
        for col in columns
    )

    if has_auto_increment:
        return 'id'
    elif has_update_time:
        return 'timestamp'
    else:
        return 'full'

def get_max_id(cursor, table_name):
    """获取当前最大ID"""
    cursor.execute(f"SELECT MAX(id) FROM {table_name}")
    return cursor.fetchone()[0] or 0

def get_last_update(cursor, table_name):
    """获取最后更新时间"""
    cursor.execute(f"SELECT MAX(update_time) FROM {table_name}")
    return cursor.fetchone()[0]

def full_sync(source_cursor, target_cursor, table_name):
    """全量同步逻辑"""
    target_cursor.execute(f"TRUNCATE TABLE {table_name}")
    source_cursor.execute(f"SELECT * FROM {table_name}")
    data = source_cursor.fetchall()
    
    if data:
        insert_query = f"INSERT INTO {table_name} VALUES ({','.join(['%s']*len(data[0]))})"
        target_cursor.executemany(insert_query, data)

def incremental_sync_by_id(source_conn, target_conn, table_name, last_id):
    """基于ID的增量同步"""
    with source_conn.cursor() as src_cur, target_conn.cursor() as tgt_cur:
        src_cur.execute(f"""
            SELECT * FROM {table_name}
            WHERE id > %s
            ORDER BY id ASC
        """, (last_id,))
        
        new_data = src_cur.fetchall()
        
        if new_data:
            insert_query = f"INSERT INTO {table_name} VALUES ({','.join(['%s']*len(new_data[0]))})"
            tgt_cur.executemany(insert_query, new_data)
            target_conn.commit()
            
            # 获取新的最大ID
            new_max_id = max(row[0] for row in new_data)
            return len(new_data), new_max_id
    return 0, last_id

def incremental_sync_by_time(source_conn, target_conn, table_name, last_time):
    """基于时间的增量同步"""
    with source_conn.cursor() as src_cur, target_conn.cursor() as tgt_cur:
        src_cur.execute(f"""
            SELECT * FROM {table_name}
            WHERE update_time > %s
            ORDER BY update_time ASC
        """, (last_time,))
        
        new_data = src_cur.fetchall()
        
        if new_data:
            insert_query = f"INSERT INTO {table_name} VALUES ({','.join(['%s']*len(new_data[0]))})"
            tgt_cur.executemany(insert_query, new_data)
            target_conn.commit()
            
            # 获取新的最大时间
            new_max_time = max(row[update_time_idx] for row in new_data)  # 需要确定字段位置
            return len(new_data), new_max_time
    return 0, last_time

def sync_table(source_conn, target_conn, source_cursor, target_cursor, table_name):
    """改进后的同步逻辑"""
    try:
        # 确定同步策略
        strategy = determine_sync_strategy(source_cursor, table_name)
        
        # 获取同步状态
        target_cursor.execute("""
            SELECT last_sync_id, last_sync_time, sync_strategy 
            FROM sync_metadata 
            WHERE table_name = %s
        """, (table_name,))
        status = target_cursor.fetchone()
        
        # 初始化同步状态
        if not status:
            if strategy == 'id':
                initial_value = get_max_id(source_cursor, table_name)
            elif strategy == 'timestamp':
                initial_value = get_last_update(source_cursor, table_name)
            else:
                initial_value = None
            
            target_cursor.execute("""
                INSERT INTO sync_metadata 
                (table_name, sync_strategy, initial_sync)
                VALUES (%s, %s, FALSE)
            """, (table_name, strategy))
            target_conn.commit()
            status = (0, None, strategy)

        last_id, last_time, strategy = status
        
        # 执行同步
        if strategy == 'id':
            count, new_id = incremental_sync_by_id(
                source_conn, target_conn, 
                table_name, last_id
            )
            # 更新元数据
            target_cursor.execute("""
                UPDATE sync_metadata 
                SET last_sync_id = %s,
                    initial_sync = TRUE
                WHERE table_name = %s
            """, (new_id, table_name))
            
        elif strategy == 'timestamp':
            count, new_time = incremental_sync_by_time(
                source_conn, target_conn,
                table_name, last_time
            )
            # 更新元数据
            target_cursor.execute("""
                UPDATE sync_metadata 
                SET last_sync_time = %s,
                    initial_sync = TRUE
                WHERE table_name = %s
            """, (new_time, table_name))
            
        else:
            full_sync(source_cursor, target_cursor, table_name)
            count = "全量"
            target_cursor.execute("""
                UPDATE sync_metadata 
                SET initial_sync = TRUE
                WHERE table_name = %s
            """, (table_name,))
        
        target_conn.commit()
        print(f"表 {table_name} 同步完成，策略：{strategy}，更新记录：{count}")

    except Exception as e:
        target_conn.rollback()
        raise e
